﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using FrankJie.IDAL;
using FrankJie.Model;

namespace FrankJie.SQLServerDAL
{
	/// <summary>
	/// 分类数据访问类
	/// </summary>
	public partial class Category:ICategory
	{
        /// <summary>
        /// 增加一个分类
        /// </summary>
        /// <param name="model">分类实体</param>
        /// <returns>返回分类ID</returns>
        public int Add(CategoryInfo categoryInfo)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("INSERT INTO tblCategory(");
            strSql.Append("ObjectType,CategoryName,Description,HaveCount,ParentId)");
			strSql.Append(" VALUES (");
            strSql.Append("@ObjectType,@CategoryName,@Description,@HaveCount,@ParentId)");
			strSql.Append(";SELECT @@IDENTITY");
			SqlParameter[] parameters = {
                    new SqlParameter("@ObjectType", SqlDbType.Int,4),
					new SqlParameter("@CategoryName", SqlDbType.VarChar,50),
					new SqlParameter("@Description", SqlDbType.VarChar,50),
					new SqlParameter("@HaveCount", SqlDbType.Int,4),
					new SqlParameter("@ParentId", SqlDbType.Int,4)};
            parameters[0].Value = categoryInfo.ObjectType;
            parameters[1].Value = categoryInfo.CategoryName;
            parameters[2].Value = categoryInfo.Description;
            parameters[3].Value = categoryInfo.HaveCount;
            parameters[4].Value = categoryInfo.ParentId;
            
			object obj = SQLHelper.GetSingle(SQLHelper.FrankJieDBConnStr,CommandType.Text,strSql.ToString(),parameters);
			if (obj == null)
			{
				return 0;
			}
			else
			{
				return Convert.ToInt32(obj);
			}
		}

        /// <summary>
        /// 增加分类所拥有的数量
        /// </summary>
        /// <param name="pid">ID</param>
        /// <param name="objectType">类别 枚举ObjectType</param>
        /// <returns>返回是否成功</returns>
        public bool AddCount(int id, int objectType)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE c SET ");
            strSql.Append("HaveCount = HaveCount + 1 ");
            strSql.Append("FROM tblCategory as c RIGHT JOIN tblObjectCategoryRelation as r on c.Id=r.CategoryId ");
            strSql.Append("WHERE r.ObjectId=@Id AND c.ObjectType=@ObjectType");
            SqlParameter[] parameters = {
                    new SqlParameter("@ObjectType", SqlDbType.Int,4),
					new SqlParameter("@Id", SqlDbType.Int,4)};
            parameters[0].Value = objectType;
            parameters[1].Value = id;

            int rows = SQLHelper.ExecuteSql(SQLHelper.FrankJieDBConnStr, CommandType.Text, strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 更新一个分类
        /// </summary>
        /// <param name="categoryInfo">分类实体</param>
        /// <returns>返回是否成功</returns>
        public bool Update(CategoryInfo categoryInfo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE tblCategory SET ");
            strSql.Append("ObjectType=@ObjectType,CategoryName=@CategoryName,Description=@Description,ParentId=@ParentId");
            strSql.Append(" WHERE Id=@Id");
            SqlParameter[] parameters = {
                    new SqlParameter("@ObjectType", SqlDbType.Int,4),
					new SqlParameter("@CategoryName", SqlDbType.VarChar,50),
					new SqlParameter("@Description", SqlDbType.VarChar,50),
					new SqlParameter("@ParentId", SqlDbType.Int,4),
					new SqlParameter("@Id", SqlDbType.Int,4)};
            parameters[0].Value = categoryInfo.ObjectType;
            parameters[1].Value = categoryInfo.CategoryName;
            parameters[2].Value = categoryInfo.Description;
            parameters[3].Value = categoryInfo.ParentId;
            parameters[4].Value = categoryInfo.Id;

            int rows = SQLHelper.ExecuteSql(SQLHelper.FrankJieDBConnStr,CommandType.Text,strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一个分类
        /// </summary>
        /// <param name="Id">分类ID</param>
        /// <returns>返回是否成功</returns>
		public bool Delete(int id)
		{
            string strSql = "DELETE FROM tblCategory WHERE Id=@Id";
			SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = id;

			int rows=SQLHelper.ExecuteSql(SQLHelper.FrankJieDBConnStr,CommandType.Text,strSql,parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}

        /// <summary>
        /// 获取分类列表
        /// </summary>
        /// <returns>返回分类集合</returns>
        public List<CategoryInfo> GetList()
        {
            string strSql = "SELECT * FROM tblObjectCategoryRelation LEFT JOIN tblCategory ON tblObjectCategoryRelation.ObjectId=tblCategory.Id";
            List<CategoryInfo> categoryInfoList = new List<CategoryInfo>();
            CategoryInfo categoryInfo = null;
            using (SqlDataReader dr = SQLHelper.ExecuteReader(SQLHelper.FrankJieDBConnStr, CommandType.Text, strSql))
            {
                categoryInfo = new CategoryInfo();
                categoryInfo.Id = dr["Id"] == null ? 0 : int.Parse(dr["Id"].ToString());
                categoryInfo.ObjectType = dr["ObjectType"] == null ? 0 : int.Parse(dr["ObjectType"].ToString());
                categoryInfo.CategoryName = dr["CategoryName"] == null? string.Empty : dr["CategoryName"].ToString();
                categoryInfo.Description = dr["Description"] == null ? string.Empty : dr["Description"].ToString();
                categoryInfo.HaveCount = dr["HaveCount"] == null ? 0 : int.Parse(dr["HaveCount"].ToString());
                categoryInfo.ParentId = dr["ParentId"] == null ? 0 : int.Parse(dr["ParentId"].ToString());
                categoryInfo.ObjectId = int.Parse(dr["ObjectId"].ToString());
                categoryInfoList.Add(categoryInfo);
            }
            return categoryInfoList;
        }
	}
}

